SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TWSABA_REPO_Q91]  
/*-------------------------------------------------------*/  
/*---Empresa              : OFISIS S.A.                --*/  
/*---Cliente              : OFISIS    --*/  
/*---Sistema              : CALCULOS SALDOS Bancos     --*/  
/*---M+dulo               : Empresa                    --*/  
/*---Programa             : CALCULA SALDO INICIAL --*/  
/*---Script               : TMSABA_M.SQL         --*/  
/*---Nombre SP            : SP_TWSABA_REPO_Q91  --*/  
/*---Desarrollado por     : DCHIANG         --*/  
/*---Fecha Creacion   : 16/02/2005         --*/   
/*---Base Datos           : SQLSERVER    --*/  
/*---Versi+n              : 8.0                        --*/  
/*---Invoca a SP          :                            --*/  
/*-------------------------------------------------------*/  
/*-------------------------------------------------------*/  
/*---Modificado 1 por     : DCHIANG         --*/  
/*---Fecha Modificaci+n   : 16/02/2005    --*/   
/*---Detalle Modificaci+n : A SQL         --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Comentarios                                       --*/  
/*---                                                  --*/  
/*---                                                  --*/  
/*-------------------------------------------------------*/  
  
/*--------------------------------------------------------------------- */  
/* Drop Proc SP_TWSABA_REPO_Q91        */  
/* Grant All On SP_TWSABA_REPO_Q91 To Public     */  
/* SP_TWSABA_REPO_Q91'10', '001' , null        */  
/*----------------------------------------------------------------------*/  
@IDFE_OPER TD_DT_001,  
@ISTI_FECH TD_VC_001, /*'O','S'*/  
@ISFE_INGR TD_VC_001, /*'O','D'*/  
@ISFE_EGRE TD_VC_001  /*'O','E','P','C'*/  
 As  
DECLARE  
@CSCO_EMPR TD_VC_002,  
@CSCO_BANC TD_VC_003,  
@CSNU_CNTA_BANC  TD_VC_020,  
@VSAA_MINI TD_VC_004,  
@VSMM_MINI TD_VC_002,  
  
  
/*Variables*/  
@VNIM_INGR      NUMERIC(16,4),  
@VNIM_EGRE  NUMERIC(16,4),  
@VNIM_SALD_INIC NUMERIC(16,4),  
@VNIM_SALD_MINI NUMERIC(16,4)  
  
  
DECLARE CU_TMCNTA_BANC_Q01_1 CURSOR FOR   
SELECT CO_EMPR , CO_BANC ,  NU_CNTA_BANC   
FROM TMCNTA_BANC   
  
  
-- PARA CALCULAR EL SALDO INICIAL DEL PRIMER PERIODO DE LAS CUENTAS  
Open CU_TMCNTA_BANC_Q01_1  
FETCH CU_TMCNTA_BANC_Q01_1 INTO @CSCO_EMPR , @CSCO_BANC , @CSNU_CNTA_BANC  
WHILE (@@FETCH_STATUS = 0)  
BEGIN  
   
 IF (@@FETCH_STATUS < 0)  
     Begin  
  RAISERROR 20001 "FIN DE ARCHIVO"  
  CLOSE CU_TMCNTA_BANC_Q01_1  
  DEALLOCATE CU_TMCNTA_BANC_Q01_1  
  RETURN  
     End  
  
  
SELECT @VSAA_MINI = MIN( AA_BNCO)  
FROM TMSABA_MENS  
WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)  
AND RTRIM(CO_BANC) = RTRIM(@CSCO_BANC)  
AND RTRIM(NU_CNTA_BANC) = RTRIM(@CSNU_CNTA_BANC)  
  
SELECT @VSMM_MINI = MIN( MM_BNCO)  
FROM TMSABA_MENS  
WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)  
AND RTRIM(CO_BANC) = RTRIM(@CSCO_BANC)  
AND RTRIM(NU_CNTA_BANC) = RTRIM(@CSNU_CNTA_BANC)  
AND RTRIM(AA_BNCO) = RTRIM(@VSAA_MINI)  
  
IF @VSAA_MINI IS NOT NULL AND @VSMM_MINI IS NOT NULL   
BEGIN   
  
SELECT @VNIM_SALD_MINI = IM_SALD_INIC  
FROM TMSABA_MENS  
WHERE RTRIM(CO_EMPR) = RTRIM(@CSCO_EMPR)  
AND RTRIM(CO_BANC) = RTRIM(@CSCO_BANC)  
AND RTRIM(NU_CNTA_BANC) = RTRIM(@CSNU_CNTA_BANC)  
AND RTRIM(AA_BNCO) = RTRIM(@VSAA_MINI)  
AND RTRIM(MM_BNCO) = RTRIM(@VSMM_MINI)  
  
INSERT INTO #TWSABA_REPO_AUXI( CO_EMPR, CO_BANC, NU_CNTA_BANC, IM_SALD_INIC)  
VALUES( @CSCO_EMPR, @CSCO_BANC, @CSNU_CNTA_BANC, @VNIM_SALD_MINI)  
  
  
END  
  
FETCH CU_TMCNTA_BANC_Q01_1 INTO @CSCO_EMPR , @CSCO_BANC , @CSNU_CNTA_BANC  
END  
  
CLOSE CU_TMCNTA_BANC_Q01_1  
DEALLOCATE CU_TMCNTA_BANC_Q01_1  
  
  
IF @ISTI_FECH = 'O'   
BEGIN  
  
INSERT INTO #TWSABA_REPO ( CO_EMPR, CO_BANC, NU_CNTA_BANC, IM_SALD_INIC )  
SELECT CO_EMPR, CO_BANC, NU_CNTA_BANC,   
ISNULL(SUM( ISNULL(PATINDEX( TI_MOVI_BANC, 'I')*IM_MOVI, 0) - ISNULL(PATINDEX( TI_MOVI_BANC, 'E')*IM_MOVI,0) ), 0)  
FROM TCMOVI_BANC   
WHERE FE_OPER < @IDFE_OPER  
AND TI_SITU = 'ACT'   
GROUP BY CO_EMPR, CO_BANC, NU_CNTA_BANC  
  
END -- FIN @ISTI_FECH = 'O'  
  
  
IF @ISTI_FECH = 'S'   
  
BEGIN  
  
-- CURSOR DE TODAS LAS CUENTAS  
DECLARE CU_TMCNTA_BANC_Q01_2 CURSOR FOR   
SELECT CO_EMPR , CO_BANC ,  NU_CNTA_BANC   
FROM TMCNTA_BANC  
  
Open CU_TMCNTA_BANC_Q01_2  
FETCH CU_TMCNTA_BANC_Q01_2 INTO @CSCO_EMPR , @CSCO_BANC , @CSNU_CNTA_BANC  
  
WHILE (@@FETCH_STATUS = 0)  
BEGIN  
   
 IF (@@FETCH_STATUS < 0)  
     Begin  
  RAISERROR 20001 "FIN DE ARCHIVO"  
  CLOSE CU_TMCNTA_BANC_Q01_2  
  DEALLOCATE CU_TMCNTA_BANC_Q01_2  
  RETURN  
     End  
  
  
  
IF @ISFE_INGR = 'D'  
BEGIN   
  
SELECT @VNIM_INGR = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
FROM TCMOVI_BANC   
WHERE TI_SITU = 'ACT'  
AND TI_MOVI_BANC = 'I'  
AND CO_EMPR = @CSCO_EMPR  
AND CO_BANC = @CSCO_BANC  
AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
AND(  ( FE_DISP < @IDFE_OPER  
AND NU_CHEQ IS NOT NULL) Or  
( FE_OPER < @IDFE_OPER  
AND NU_CHEQ IS NULL ) )  
  
  
 IF @ISFE_EGRE = 'O'   
 BEGIN  
  SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
  FROM TCMOVI_BANC   
  WHERE TI_SITU = 'ACT'  
  AND TI_MOVI_BANC = 'E'  
  AND CO_EMPR = @CSCO_EMPR  
  AND CO_BANC = @CSCO_BANC  
  AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
   AND FE_OPER < @IDFE_OPER  
    
 END -- @ISFE_EGRE = 'O'   
  
  
 IF @ISFE_EGRE = 'E'   
 BEGIN  
  SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
  FROM TCMOVI_BANC   
  WHERE TI_SITU = 'ACT'  
  AND TI_MOVI_BANC = 'E'   
  AND CO_EMPR = @CSCO_EMPR  
  AND CO_BANC = @CSCO_BANC  
  AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
  AND ( ( FE_ENTR_CHEQ < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NULL) OR  
  ( FE_OPER < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NOT NULL) )  
    
 END -- @ISFE_EGRE = 'E'   
  
 IF @ISFE_EGRE = 'P'   
 BEGIN  
  SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
  FROM TCMOVI_BANC   
  WHERE TI_SITU = 'ACT'  
  AND TI_MOVI_BANC = 'E'   
  AND CO_EMPR = @CSCO_EMPR  
  AND CO_BANC = @CSCO_BANC  
  AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
  AND ( ( FE_PROG_CHEQ < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NULL) OR  
  ( FE_OPER < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NOT NULL) )  
 END -- @ISFE_EGRE = 'P'   
  
 IF @ISFE_EGRE = 'C'   
 BEGIN  
  SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
  FROM TCMOVI_BANC   
  WHERE TI_SITU = 'ACT'  
  AND TI_MOVI_BANC = 'E'   
  AND CO_EMPR = @CSCO_EMPR  
  AND CO_BANC = @CSCO_BANC  
  AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
  AND ( ( FE_CHEQ < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NULL) OR  
  ( FE_OPER < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NOT NULL) )  
 END -- @ISFE_EGRE = 'C'   
  
 SELECT VNIM_SALD_INIC = ISNULL(@VNIM_INGR,0) - ISNULL(@VNIM_EGRE,0)  
  
END -- @ISFE_INGR = 'D'   
  
IF @ISFE_INGR = 'O'   
BEGIN  
  
SELECT @VNIM_INGR = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
FROM TCMOVI_BANC   
WHERE TI_SITU = 'ACT'  
AND TI_MOVI_BANC = 'I'  
AND CO_EMPR = @CSCO_EMPR  
AND CO_BANC = @CSCO_BANC  
AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
AND FE_OPER < @IDFE_OPER  
  
 IF @ISFE_EGRE = 'O'   
 BEGIN  
  SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
  FROM TCMOVI_BANC   
  WHERE TI_SITU = 'ACT'  
  AND TI_MOVI_BANC = 'E'  
  AND CO_EMPR = @CSCO_EMPR  
  AND CO_BANC = @CSCO_BANC  
  AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
   AND FE_OPER < @IDFE_OPER  
 END -- @ISFE_EGRE = 'O'   
  
  
 IF @ISFE_EGRE = 'E'   
 BEGIN  
  SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
  FROM TCMOVI_BANC   
  WHERE TI_SITU = 'ACT'  
  AND TI_MOVI_BANC = 'E'   
  AND CO_EMPR = @CSCO_EMPR  
  AND CO_BANC = @CSCO_BANC  
  AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
  AND ( ( FE_ENTR_CHEQ < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NULL) OR  
  ( FE_OPER < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NOT NULL) )  
 END --ISFE_EGRE = 'E'   
  
 IF @ISFE_EGRE = 'P'   
 BEGIN  
  SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
  FROM TCMOVI_BANC   
  WHERE TI_SITU = 'ACT'  
  AND TI_MOVI_BANC = 'E'   
  AND CO_EMPR = @CSCO_EMPR  
  AND CO_BANC = @CSCO_BANC  
  AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
  AND ( ( FE_PROG_CHEQ < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NULL) OR  
  
  
  ( FE_OPER < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NOT NULL) )  
 END -- @ISFE_EGRE = 'P'    
  
 IF @ISFE_EGRE = 'C'   
 BEGIN  
  SELECT @VNIM_EGRE = ISNULL(SUM ( ISNULL(IM_MOVI,0)) , 0)  
  FROM TCMOVI_BANC   
  WHERE TI_SITU = 'ACT'  
  AND TI_MOVI_BANC = 'E'   
  AND CO_EMPR = @CSCO_EMPR  
  AND CO_BANC = @CSCO_BANC  
  AND NU_CNTA_BANC = @CSNU_CNTA_BANC   
  AND ( ( FE_CHEQ < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NULL) OR  
  ( FE_OPER < @IDFE_OPER  
  AND CO_TIPO_DOCU IS NOT NULL) )  
 END -- @ISFE_EGRE = 'C'   
  
SELECT @VNIM_SALD_INIC = ISNULL(@VNIM_INGR, 0) - ISNULL( @VNIM_EGRE,0)  
  
END -- @ISFE_INGR = 'O'   
  
INSERT INTO #TWSABA_REPO(CO_EMPR, CO_BANC, NU_CNTA_BANC, IM_SALD_INIC )  
VALUES( @CSCO_EMPR, @CSCO_BANC, @CSNU_CNTA_BANC, @VNIM_SALD_INIC )  
  
  
  -- FIN @ISTI_FECH = 'S'   
  
  
FETCH CU_TMCNTA_BANC_Q01_2 INTO @CSCO_EMPR , @CSCO_BANC , @CSNU_CNTA_BANC  
END  
  
CLOSE CU_TMCNTA_BANC_Q01_2  
DEALLOCATE CU_TMCNTA_BANC_Q01_2  
  
END  
  
-- INSERTO CUENTAS QUE NO HAN TENIDO MOVIMIENTOS, ES DECIR AUN NO ESTAN GRABADAS EN TABLA #TWSABA REPO  
  
INSERT INTO #TWSABA_REPO( CO_EMPR, CO_BANC, NU_CNTA_BANC, IM_SALD_INIC )  
SELECT T2.CO_EMPR, T2.CO_BANC, T2.NU_CNTA_BANC, 0  
FROM TMCNTA_BANC T2  
WHERE NOT EXISTS  
(SELECT T1.CO_EMPR FROM #TWSABA_REPO T1  
WHERE RTRIM(T1.CO_EMPR) = RTRIM(T2.CO_EMPR)  
AND RTRIM(T1.CO_BANC) = RTRIM(T2.CO_BANC)   
AND RTRIM(T1.NU_CNTA_BANC) = RTRIM(T2.NU_CNTA_BANC))  
  
-- ACTUALIZO EL SALDO INICIAL MAS EL SALDO INICIAL DEL PRIMER PERIODO QUE SE ABRIO  
UPDATE #TWSABA_REPO   
SET #TWSABA_REPO.IM_SALD_INIC =  ( SELECT ISNULL(T2.IM_SALD_INIC,0) + ISNULL(#TWSABA_REPO.IM_SALD_INIC,0) FROM #TWSABA_REPO_AUXI T2  
         WHERE RTRIM(#TWSABA_REPO.CO_EMPR) = RTRIM(T2.CO_EMPR)  
         AND RTRIM(#TWSABA_REPO.CO_BANC) = RTRIM(T2.CO_BANC)  
         AND RTRIM(#TWSABA_REPO.NU_CNTA_BANC) = RTRIM(T2.NU_CNTA_BANC))  
WHERE EXISTS( SELECT T3.CO_EMPR  
FROM #TWSABA_REPO_AUXI T3  
WHERE #TWSABA_REPO.CO_EMPR = T3.CO_EMPR  
AND #TWSABA_REPO.CO_BANC = T3.CO_BANC  
AND #TWSABA_REPO.NU_CNTA_BANC = T3.NU_CNTA_BANC )  
  
/*------------------------------ FIN ---------------------------------*/


GO
